package com.houduan.dao;

import com.houduan.model.Computer;
import com.houduan.model.Session;
import com.houduan.model.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SessionDao {

    private static final String URL = "jdbc:mysql://localhost:3306/netbar";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "rjxy123456";

    // SQL 语句
    private static final String INSERT_SESSION_SQL = "INSERT INTO sessions (user_id, computer_id, start_time) VALUES (?, ?, CURRENT_TIMESTAMP)";
    private static final String UPDATE_SESSION_SQL = "UPDATE sessions SET end_time = CURRENT_TIMESTAMP, duration = ?, cost = ? WHERE id = ?";
    private static final String SELECT_SESSION_BY_ID_SQL = "SELECT * FROM sessions WHERE id = ?";
    private static final String SELECT_ALL_SESSIONS_SQL =
            "SELECT s.*, u.username, c.name AS computer_name, c.price AS computer_price " +
                    "FROM sessions s " +
                    "INNER JOIN users u ON s.user_id = u.id " +
                    "INNER JOIN computers c ON s.computer_id = c.id " +
                    "ORDER BY s.start_time DESC";

    // 获取数据库连接
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    // 开始上机（插入新记录）
    public void startSession(Session session) {
        String sql = "INSERT INTO sessions (user_id, computer_id, start_time, duration_minutes) VALUES (?, ?, NOW(), ?)";
        try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, session.getUser().getId());
            stmt.setInt(2, session.getComputer().getId());
            stmt.setInt(3, (int) session.getDuration());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    // 结束上机（更新记录）
    public void endSession(int sessionId, double duration, double cost) {
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_SESSION_SQL)) {
            preparedStatement.setDouble(1, duration);
            preparedStatement.setDouble(2, cost);
            preparedStatement.setInt(3, sessionId);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据 ID 获取上机记录
    public Session getSessionById(int sessionId) {
        Session session = null;
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_SESSION_BY_ID_SQL)) {
            preparedStatement.setInt(1, sessionId);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    session = mapResultSetToSession(resultSet);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return session;
    }

    // 获取所有上机记录
    public List<Session> getAllSessions() {
        List<Session> sessions = new ArrayList<>();
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_SESSIONS_SQL);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                sessions.add(mapResultSetToSession(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sessions;
    }

    // 计算上机时长
    public double calculateDuration(int sessionId) {
        String sql = "SELECT start_time, end_time FROM sessions WHERE id = ?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            preparedStatement.setInt(1, sessionId);
            ResultSet resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                Timestamp startTime = resultSet.getTimestamp("start_time");
                Timestamp endTime = resultSet.getTimestamp("end_time");

                long endMillis = (endTime != null) ? endTime.getTime() : System.currentTimeMillis();
                long startMillis = startTime.getTime();

                // 计算时长（小时）
                return (endMillis - startMillis) / (1000.0 * 60 * 60);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


    // 工具方法：将 ResultSet 映射为 Session 对象
    private Session mapResultSetToSession(ResultSet resultSet) throws SQLException {
        int id = resultSet.getInt("id");
        int userId = resultSet.getInt("user_id");
        int computerId = resultSet.getInt("computer_id");
        Timestamp startTime = resultSet.getTimestamp("start_time");
        Timestamp endTime = resultSet.getTimestamp("end_time");
        double duration = resultSet.getDouble("duration");
        double cost = resultSet.getDouble("cost");

        // 获取关联的用户和电脑
        User user = new UserDao().getUserById(userId);
        Computer computer = new ComputerDao().getComputerById(computerId);

        return new Session(id, user, computer, startTime, endTime, duration, cost);
    }
    public int getLastSessionId() {
        String sql = "SELECT MAX(id) AS last_id FROM sessions"; // 假设 `sessions` 表中主键列为 `id`
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                return rs.getInt("last_id"); // 获取最新的会话 ID
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1; // 如果失败，返回 -1
    }

}
